﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace TanHungLong
{
    public class clDB
    {
        public bool CheckBBExist(string DBname)
        {
            string DBConnectionStr = "Data Source=(local)\\SQLEXPRESS;Initial Catalog=master;Trusted_Connection=true;";
            bool hasBD = false;

            try
            {
                using (SqlConnection cnn = new SqlConnection(DBConnectionStr))
                {
                    cnn.Open();
                    string scmd = "SELECT name AS DBName FROM sys.databases";

                    using (SqlCommand cmd = new SqlCommand(scmd, cnn))
                    {
                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                if (((string)reader[0]).ToUpper() == DBname)
                                {
                                    hasBD = true;
                                    break;
                                }
                            }

                            reader.Close();
                            reader.Dispose();
                        }

                        cmd.Dispose();
                    }

                    cnn.Close();
                    cnn.Dispose();
                }
            }
            catch { }

            return hasBD;
        }

        public void AttactDB(string mdfFile, string dbname)
        {
            string DBConnectionStr = "Data Source=(local)\\SQLEXPRESS;Initial Catalog=master;Trusted_Connection=true;";
            try
            {
                using (SqlConnection cnn = new SqlConnection(DBConnectionStr))
                {
                    cnn.Open();
                    string scmd = "sp_attach_single_file_db @dbname = '" + dbname + "', @physname = N'" + mdfFile + "'";
                    //string scmd = "CREATE DATABASE [" + dbname + "] ON ( FILENAME = N'" + mdfFile + "' ) FOR ATTACH_REBUILD_LOG";
                    SqlCommand cmd = new SqlCommand(scmd, cnn);
                    cmd.ExecuteNonQuery();
                    scmd = "if not exists (select name from master.sys.databases sd where name = N'" + dbname + "' and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() ) EXEC [" + dbname + "].dbo.sp_changedbowner @loginame=N'sa', @map=false";
                    cmd.CommandText = scmd;
                    cmd.ExecuteNonQuery();
                    cnn.Close();
                    cnn.Dispose();
                }
            }
            catch (Exception ex)
            {
                if (ex.Message.IndexOf("Unable to open the physical file") < 0)
                    throw;
            }
        }

        public void DetactDB(string dbname)
        {
            string DBConnectionStr = "Data Source=(local)\\SQLEXPRESS;Initial Catalog=master;Trusted_Connection=true;";
            try
            {
                using (SqlConnection cnn = new SqlConnection(DBConnectionStr))
                {
                    cnn.Open();
                    string scmd = "DECLARE @SQL varchar(max)\r\nSET @SQL = ''\r\nSELECT @SQL = @SQL + 'Kill ' + Convert(varchar, SPId) + ';'\r\nFROM MASTER..SysProcesses\r\nWHERE DBId = DB_ID(N'" + dbname + "') AND SPId <> @@SPId\r\nEXEC(@SQL)";
                    using (SqlCommand cmd = new SqlCommand(scmd, cnn))
                    {
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "EXEC master.dbo.sp_detach_db @dbname = N'" + dbname + "', @keepfulltextindexfile=N'true'";
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();
                    }
                    cnn.Close();
                    cnn.Dispose();
                }
            }
            catch { throw; }
        }
    }
}
